﻿using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using Dapper;
using DB.SchemaSync.Library.Models;

namespace DB.SchemaSync.SqlServer
{
	public partial class SqlServerDbProvider
	{
		private async Task<IEnumerable<Module>> GetModulesAsync(IDbConnection connection)
		{
			var modules = await connection.QueryAsync<Module>(
				@"SELECT
					[name] AS [Name],
					SCHEMA_NAME([schema_id]) AS [Schema],
					[object_id] AS [ObjectId],
					CASE [type]
						WHEN 'P' THEN 1
						WHEN 'FN' THEN 2
						WHEN 'TF' THEN 3
					END AS [Type]
				FROM
					[sys].[objects]
				WHERE
					[type] IN ('TF', 'FN', 'P')");

			// when paramter_id = 0, it refers to scalar function output, and is not a parameter in the sense I mean it here
			var parameters = await connection.QueryAsync<Parameter>(
				@"SELECT
					[object_id] AS [ObjectId],
					[name] AS [Name],
					TYPE_NAME([p].[system_type_id]) AS [DataType],
					CASE
						WHEN TYPE_NAME([p].[system_type_id]) LIKE 'nvar%' AND [p].[max_length]>0 THEN ([p].[max_length]/2)
						WHEN TYPE_NAME([p].[system_type_id]) LIKE 'nvar%' AND [p].[max_length]=0 THEN -1
						ELSE [p].[max_length]
					END AS [MaxLength],
					[parameter_id] AS [Position]
				FROM
					[sys].[parameters] [p]
				WHERE
					[parameter_id] > 0");
			var paramLookup = parameters.ToLookup(row => row.ObjectId);

			// sp_helptext seems to be the only way to preserve the original whitespace in proc definitions
			foreach (var m in modules)
			{
				var lines = await connection.QueryAsync<string>($"sp_helptext", new { objname = $"{m.Schema}.{m.Name}" }, commandType: CommandType.StoredProcedure);
				m.Body = string.Join(string.Empty, lines).Trim();
				m.BodyHash = GetBodyHash(m.Body, out string normalized);
				m.NormalizedDefinition = normalized;
				m.Parameters = paramLookup[m.ObjectId].ToArray();
				foreach (var p in m.Parameters) p.Module = m;
			}

			return modules;
		}

		private byte[] GetBodyHash(string body, out string normalized)
		{
			normalized = Regex.Replace(body, @"\s+", " ").ToLower();
			var bytes = Encoding.UTF8.GetBytes(normalized);
			using (MD5 hash = MD5.Create())
			{
				return hash.ComputeHash(bytes);
			}
		}
	}
}